package com.weilx959.spt.plugin.e2sp.service;

import com.alibaba.fastjson2.JSON;
import com.weilx959.spt.core.utils.Assert;
import com.weilx959.spt.plugin.e2sp.constant.Dialect;
import com.weilx959.spt.plugin.e2sp.constant.ExcelToSqlConstants;
import com.weilx959.spt.plugin.e2sp.constant.ExcelType;
import com.weilx959.spt.plugin.e2sp.constant.SQLType;
import com.weilx959.spt.plugin.e2sp.exception.ExcelException;
import com.weilx959.spt.plugin.e2sp.model.ExcelVO;
import com.weilx959.spt.plugin.e2sp.model.TransformedExcelVO;
import com.weilx959.spt.plugin.e2sp.model.statement.CheckStatement;
import com.weilx959.spt.plugin.e2sp.model.statement.DeleteStatement;
import com.weilx959.spt.plugin.e2sp.model.statement.InsertStatement;
import com.weilx959.spt.plugin.e2sp.model.statement.ParentStatement;
import com.weilx959.spt.plugin.e2sp.utils.ExcelUtil;
import com.weilx959.spt.plugin.e2sp.utils.ResourceBundleUtil;
import javafx.collections.FXCollections;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
import java.util.stream.Collectors;

/**
 * excel to sql服务
 *
 * @author weilx
 * @since 2024-04-27
 */
@Slf4j
public class ExcelToSqlService {
    private static final ResourceBundle bundle = ResourceBundleUtil.get();

    /**
     * 从 filePath 读取 excel 文件，并转换为 {@link ExcelVO} 对象
     *
     * @param excelFile excel 文件
     * @return {@link ExcelVO}
     */
    public ExcelVO read(File excelFile) {
        log.info("Read excel from file: {}", excelFile);
        Assert.notNull(excelFile, String.format(bundle.getString("e2s.main.message.param-is-null"), "filePath"));
        String fileName = excelFile.getName();

        ExcelVO excelVO = ExcelVO.builder()
            .name(fileName)
            .type(ExcelType.getBySuffix(fileName.substring(fileName.lastIndexOf(ExcelToSqlConstants.DOT) + 1)))
            .path(excelFile.getAbsolutePath())
            .build();
        try (InputStream inputStream = Files.newInputStream(excelFile.toPath())) {
            Workbook workbook = WorkbookFactory.create(inputStream);
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            int numberOfSheets = workbook.getNumberOfSheets();
            excelVO.setSheets(new ArrayList<>(numberOfSheets));

            // 遍历每个sheet
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int lastRowNum = sheet.getLastRowNum(); // from 0
                int lastColumnNum = ExcelUtil.getMaxColumnNum(sheet, formulaEvaluator); // from 1
                ExcelVO.SheetVO sheetVO = ExcelVO.SheetVO.builder().name(sheet.getSheetName()).index(i).rows(new ArrayList<>(lastRowNum)).build();
                excelVO.getSheets().add(sheetVO);

                // 遍历每个sheet的行
                for (int j = 0; j <= lastRowNum; j++) {
                    Row row = sheet.getRow(j);

                    if (row == null) { // 空行代表结束
                        break;
                    }
                    ExcelVO.RowVO rowVO = ExcelVO.RowVO.builder().index(j).cells(new ArrayList<>(lastColumnNum)).build();

                    // 遍历每个sheet的列
                    for (int k = 0; k < lastColumnNum; k++) {
                        // 支持多种类型问值读取
                        rowVO.getCells().add(ExcelUtil.getCellValue(row.getCell(k), formulaEvaluator));
                    }
                    // 如果所有的cell都是空的就结束
                    if (rowVO.getCells().stream().allMatch(e -> Objects.isNull(e) || StringUtils.isBlank(String.valueOf(e)))) {
                        break;
                    }
                    sheetVO.getRows().add(rowVO);
                }
            }
        } catch (Exception e) {
            throw new ExcelException(excelFile.toPath(), bundle.getString("e2s.main.message.file-read-failed"), e);
        }
        return excelVO;
    }

    /**
     * 转换 excel 文件为 {@link TransformedExcelVO} 对象
     *
     * @param excelVO      excel vo
     * @param transExcelVO 转换后的excel vo
     */
    public void transform(ExcelVO excelVO, TransformedExcelVO transExcelVO) {
        Assert.notNull(excelVO, String.format(bundle.getString("e2s.main.message.param-is-null"), "excelVO"));
        transExcelVO.setName(excelVO.getName());
        transExcelVO.getTables().clear();
        Optional.ofNullable(excelVO.getPath())
            .ifPresent(excelPath -> transExcelVO.setOutputDir(Paths.get(excelPath).getParent().toString()));
        List<ExcelVO.SheetVO> sheets = excelVO.getSheets();
        Assert.notEmpty(sheets, bundle.getString("e2s.main.message.convert-failed") + " No Data");

        for (ExcelVO.SheetVO sheetVO : sheets) {
            // 一个sheet为一个table
            String sheetName = sheetVO.getName();
            List<ExcelVO.RowVO> rows = sheetVO.getRows();

            if (CollectionUtils.isEmpty(rows) || rows.size() <= 1) {
                log.warn("sheet ({}) No Data", sheetName);
                continue;
            }
            String schema = ExcelUtil.getSchemaName(sheetName);
            String tableName = ExcelUtil.getTableName(sheetName);
            TransformedExcelVO.TableVO tableVO = TransformedExcelVO.TableVO.builder()
                .sheetName(sheetName)
                .name(tableName)
                .schema(schema)
                .index(sheetVO.getIndex())
                .build();
            transExcelVO.getTables().add(tableVO);

            // 第一行为列名
            List<Object> columnNames = rows.get(0).getCells();
            List<String> keys = columnNames.stream()
                .map(String::valueOf)
                .filter(cell -> cell.startsWith(ExcelToSqlConstants.TABLE_KEY_COLUMN_PREFIX))
                .map(cell -> StringUtils.removeStart(cell, ExcelToSqlConstants.TABLE_KEY_COLUMN_PREFIX))
                .toList();

            if (CollectionUtils.isEmpty(keys)) {
                log.warn("sheet ({}) No PK", sheetName);
                continue;
            }
            List<String> columns = columnNames.stream()
                .map(String::valueOf)
                .map(cell -> StringUtils.removeStart(cell, ExcelToSqlConstants.TABLE_KEY_COLUMN_PREFIX))
                .toList();

            for (int i = 1; i < rows.size(); i++) {
                ExcelVO.RowVO rowVO = rows.get(i);
                // 一个行为一个记录
                tableVO.getRecordList().add(TransformedExcelVO.RecordVO.builder()
                    .index(rowVO.getIndex())
                    .keys(FXCollections.observableList(keys))
                    .columns(FXCollections.observableList(columns))
                    .values(FXCollections.observableList(rowVO.getCells()))
                    .build());
            }
        }
    }

    /**
     * 生成 SQL 语句
     *
     * @param transExcel trans-excel
     */
    public void generateStatement(TransformedExcelVO transExcel) {
        Assert.notNull(transExcel, String.format(bundle.getString("e2s.main.message.param-is-null"), "transExcel"));
        Assert.notEmpty(transExcel.getTables(), String.format(bundle.getString("e2s.main.message.param-is-null"), "tables"));
        Assert.notNull(transExcel.getOutputDir(), String.format(bundle.getString("e2s.main.message.param-is-null"), "OutputDir"));
        Path outputPath = Paths.get(transExcel.getOutputDir());
        Dialect dialect = Optional.ofNullable(transExcel.getDialect()).orElse(ExcelToSqlConstants.DEFAULT_DIALECT);
        String defaultSchema = transExcel.getDefaultSchema();
        boolean check = transExcel.isCheck();
        String checkTemplate = Optional.ofNullable(transExcel.getCheckTemplate())
            .filter(StringUtils::isNotBlank)
            .orElse(ExcelToSqlConstants.DEFAULT_CHECK_SQL_TEMPLATE);
        List<ParentStatement> statementList = new ArrayList<>();
        List<ParentStatement> checkStatementList = new ArrayList<>();

        for (TransformedExcelVO.TableVO table : transExcel.getTables()) {
            int sheetIndex = table.getIndex();
            String tableName = table.getName();
            String schema = Optional.ofNullable(table.getSchema()).orElse(defaultSchema);
            List<TransformedExcelVO.RecordVO> recordList = table.getRecordList();

            if (CollectionUtils.isEmpty(recordList)) {
                log.warn("Table ({}) No Data", tableName);
                continue;
            }
            // check 语句处理：以key-value分组，相同则存在一条语句
            Map<String, CheckStatement> checkMap = new HashMap<>();

            for (TransformedExcelVO.RecordVO recordVO : recordList) {
                ParentStatement statement = ParentStatement.builder()
                    .sheetIndex(sheetIndex)
                    .sheetName(tableName)
                    .rowIndex(recordVO.getIndex())
                    .dialect(dialect)
                    .schema(schema)
                    .tableName(tableName)
                    .keys(recordVO.getKeys())
                    .columns(recordVO.getColumns())
                    .values(recordVO.getValues())
                    .build();
                // 默认生成insert语句
                InsertStatement insertStatement = new InsertStatement(statement);
                statementList.add(insertStatement);
                String keyValues = JSON.toJSONString(statement.getKeyColumnMap());

                if (checkMap.containsKey(keyValues)) {
                    checkMap.get(keyValues).addInsertStatement(insertStatement);
                } else {
                    DeleteStatement deleteStatement = new DeleteStatement(statement);
                    statementList.add(deleteStatement);

                    CheckStatement checkStatement = new CheckStatement(statement, checkTemplate);
                    checkStatement.setDeleteStatement(deleteStatement); // 添加delete引用
                    checkStatement.addInsertStatement(insertStatement); // 添加insert引用
                    checkMap.put(keyValues, checkStatement);
                }
            }
            checkStatementList.addAll(checkMap.values());
        }
        if (CollectionUtils.isEmpty(statementList)) {
            throw new ExcelException(bundle.getString("e2s.submit.message.no-statement-found"));
        }
        String fileName = transExcel.getName().substring(0, transExcel.getName().lastIndexOf(ExcelToSqlConstants.DOT));
        // 解析 delete & insert 语句
        this.parseSql(statementList);
        this.writeToFile(statementList, outputPath.resolve(fileName + "-SQL.sql"));

        if (!check) {
            log.info("Do not generate check statement!");
            return;
        }
        // 解析 check 语句
        this.parseSql(checkStatementList);
        this.writeToFile(checkStatementList, outputPath.resolve(fileName + "-CHECK.sql"));
    }

    /**
     * 解析sql，并设置到 {@link ParentStatement} 对象中
     *
     * @param statementList 语句列表
     */
    private void parseSql(List<ParentStatement> statementList) {
        if (CollectionUtils.isEmpty(statementList)) {
            return;
        }
        var countDownLatch = new CountDownLatch(statementList.size());
        var cachedThreadPool = Executors.newCachedThreadPool();

        for (ParentStatement statement : statementList) {
            var parser = Optional.ofNullable(statement.getType()).map(SQLType::getParser).orElse(null);

            if (Objects.isNull(parser)) {
                log.error("SQL type {} is not supported", statement.getType());
                countDownLatch.countDown();
                continue;
            }
            cachedThreadPool.execute(() -> {
                statement.setSql(parser.parse(statement));
                countDownLatch.countDown();
            });
        }
        try {
            if (!countDownLatch.await(30, TimeUnit.SECONDS)) {
                throw new TimeoutException("SQL generation timeout");
            }
        } catch (InterruptedException | TimeoutException e) {
            log.error("SQL generation failed", e);
            Thread.currentThread().interrupt();
        } finally {
            cachedThreadPool.shutdown();
        }
    }

    /**
     * 写入文件
     *
     * @param statementList 语句列表
     * @param outputFile    输出文件
     */
    private void writeToFile(List<ParentStatement> statementList, Path outputFile) {
        statementList = Optional.ofNullable(statementList)
            .orElseGet(ArrayList::new)
            .stream()
            .filter(statement -> StringUtils.isNotBlank(statement.getSql()))
            .toList();

        if (CollectionUtils.isEmpty(statementList)) {
            log.warn("No Data！");
            return;
        }
        Map<Integer, List<ParentStatement>> sheetMap = statementList.stream()
            .collect(Collectors.groupingBy(ParentStatement::getSheetIndex));
        try (OutputStream os = Files.newOutputStream(outputFile, StandardOpenOption.CREATE, StandardOpenOption.TRUNCATE_EXISTING);
             BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(os, StandardCharsets.UTF_8))) {
            // 按sheet顺序写入文件
            List<Integer> sheetIndexList = sheetMap.keySet().stream().sorted().toList();

            for (Integer sheetIndex : sheetIndexList) {
                // 按行顺序写入文件
                Map<Integer, List<ParentStatement>> rowMap = sheetMap.get(sheetIndex)
                    .stream()
                    .collect(Collectors.groupingBy(ParentStatement::getRowIndex));
                List<Integer> rowIndexList = rowMap.keySet().stream().sorted().toList();

                for (int j = 0; j < rowIndexList.size(); j++) {
                    List<ParentStatement> oneRowStatements = rowMap.get(rowIndexList.get(j));

                    // 第一行写入表名
                    if (j == 0) {
                        writer.write(String.format("-- %s.%s", oneRowStatements.get(0).getSchema(), oneRowStatements.get(0).getTableName()));
                        writer.newLine();
                    }
                    List<String> sqls = oneRowStatements.stream()
                        .sorted(Comparator.comparingInt(statement -> statement.getType().getSort()))
                        .map(ParentStatement::getSql)
                        .toList();

                    for (String sql : sqls) {
                        writer.write(sql);
                        writer.newLine();
                    }
                    writer.newLine(); // 空行分隔
                }
            }
        } catch (IOException e) {
            throw new ExcelException(outputFile, bundle.getString("e2s.submit.message.save-sql-failed"), e);
        }
    }
}
